import os

import openpyxl
import win32ui
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

import tools_pmc

print("**BOM差异分析工具**\n运行前请先导出需要对比品号的尾阶用量，运行结果默认保存在桌面！")
fill_blue = PatternFill(patternType="solid", fgColor="95B3D7")

dlg = win32ui.CreateFileDialog(1)  # 1表示打开文件对话框
dlg.SetOFNInitialDir('//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/Temp')  # 设置打开文件对话框中的初始显示目录
dlg.DoModal()
filename = dlg.GetPathName()  # 获取选择的文件名称
print(filename)
lj = filename.replace('\\', "/")
wb1 = load_workbook(lj)
ws1 = wb1.worksheets[0]

dlg = win32ui.CreateFileDialog(1)  # 1表示打开文件对话框
dlg.SetOFNInitialDir('//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/Temp')  # 设置打开文件对话框中的初始显示目录
dlg.DoModal()
filename = dlg.GetPathName()  # 获取选择的文件名称
print(filename)
lj = filename.replace('\\', "/")
wb2 = load_workbook(lj)
ws2 = wb2.worksheets[0]

list1_1 = []
list1_2 = []
list1_3 = []
list1_4 = []
for i in range(3, ws1.max_row + 1):
    if ws1.cell(i, 2).value in list1_1:
        list1_2[list1_1.index(ws1.cell(i, 2).value)] += ws1.cell(i, 12).value
    else:
        list1_1.append(ws1.cell(i, 2).value)
        list1_2.append(ws1.cell(i, 12).value)
        list1_3.append(ws1.cell(i, 4).value)
        list1_4.append(ws1.cell(i, 5).value)

list2_1 = []
list2_2 = []
list2_3 = []
list2_4 = []
for i in range(3, ws2.max_row + 1):
    if ws2.cell(i, 2).value in list2_1:
        list2_2[list2_1.index(ws2.cell(i, 2).value)] += ws2.cell(i, 12).value
    else:
        list2_1.append(ws2.cell(i, 2).value)
        list2_2.append(ws2.cell(i, 12).value)
        list2_3.append(ws2.cell(i, 4).value)
        list2_4.append(ws2.cell(i, 5).value)

wb = openpyxl.Workbook()
ws = wb.worksheets[0]
a = 3
for i in list1_1:
    if i in list2_1:
        if list1_2[list1_1.index(i)] != list2_2[list2_1.index(i)]:
            ws.cell(a, 1).value = i
            ws.cell(a, 2).value = list1_3[list1_1.index(i)]
            ws.cell(a, 3).value = list1_4[list1_1.index(i)]
            ws.cell(a, 4).value = list1_2[list1_1.index(i)]
            ws.cell(a, 5).value = i
            ws.cell(a, 6).value = list2_3[list2_1.index(i)]
            ws.cell(a, 7).value = list2_4[list2_1.index(i)]
            ws.cell(a, 8).value = list2_2[list2_1.index(i)]
            a += 1

for i in list1_1:
    if i not in list2_1:
        ws.cell(a, 1).value = i
        ws.cell(a, 2).value = list1_3[list1_1.index(i)]
        ws.cell(a, 3).value = list1_4[list1_1.index(i)]
        ws.cell(a, 4).value = list1_2[list1_1.index(i)]
        a += 1

for i in list2_1:
    if i not in list1_1:
        ws.cell(a, 5).value = i
        ws.cell(a, 6).value = list2_3[list2_1.index(i)]
        ws.cell(a, 7).value = list2_4[list2_1.index(i)]
        ws.cell(a, 8).value = list2_2[list2_1.index(i)]
        a += 1

ws.cell(1, 1).value = ws1.cell(2, 1).value
ws.cell(1, 2).value = ws1.cell(2, 4).value
ws.cell(1, 3).value = ws1.cell(2, 5).value

ws.cell(1, 5).value = ws2.cell(2, 1).value
ws.cell(1, 6).value = ws2.cell(2, 4).value
ws.cell(1, 7).value = ws2.cell(2, 5).value

list_title = ["品号", "品名", "规格", "组成用量", "品号", "品名", "规格", "组成用量"]
list_leng = [14, 32, 32, 14, 14, 32, 32, 14]
for i in range(1, 9):
    ws.cell(2, i).value = list_title[i - 1]
    ws.cell(2, i).fill = fill_blue
    ws.column_dimensions[get_column_letter(i)].width = list_leng[i - 1]

for i in range(2, ws.max_row + 1):
    if ws.cell(i, 1).value is not None:
        temp = ws.cell(i, 1).value
    else:
        temp = ws.cell(i, 5).value
    for j in range(1, 31):
        ws.cell(i, j+8).value = tools_pmc.vlookup(temp, tools_pmc.table_array_wlxxb, j + 3)

desktop_path = os.path.join(os.environ['USERPROFILE'], 'Desktop')
try:
    wb.save(desktop_path.replace('\\', "/") + "/" + ws1.cell(2, 1).value
            + "与" + ws2.cell(2, 1).value + "BOM差异.xlsx")
except PermissionError:
    print("文件已打开，请关闭后重新运行！")

print(ws1.cell(2, 1).value + "  " + ws1.cell(2, 5).value + "\n"
      + ws2.cell(2, 1).value + "  " + ws2.cell(2, 5).value + "\nsucceed!")
print(desktop_path.replace('\\', "/") + "/" + ws1.cell(2, 1).value
      + "与" + ws2.cell(2, 1).value + "BOM差异.xlsx")

try:
    # 调用系统的默认程序打开文件
    os.startfile(desktop_path.replace('\\', "/") + "/" + ws1.cell(2, 1).value
                 + "与" + ws2.cell(2, 1).value + "BOM差异.xlsx")
except Exception as e:
    print("无法打开文件:", str(e))
# input("输入意键结束！")
